from flask import Flask, render_template, request, redirect, url_for, flash, session,jsonify
import mysql.connector
import hashlib
import mysql.connector
import matplotlib.pyplot as plt
import pandas as pd
import mysql.connector
from io import BytesIO
import base64
from flask import send_file


app = Flask(__name__)
app.secret_key = 'supersecretkey'

# 配置MySQL数据库
config = {
    'user': 'root',
    'password': 'kyds2333',
    'host': 'localhost',
    'database': 'pythonfinal'
}
conn = mysql.connector.connect(**config)
cursor = conn.cursor()

def hash_password(password):
    return hashlib.sha256(password.encode()).hexdigest()


# 增删改查函数
def add_user(user_id, username, password, role_id):
    add_user_query = "INSERT INTO users (UserID, Username, Password, RoleID) VALUES (%s, %s, %s, %s)"
    user_data = (user_id, username, hash_password(password), role_id)
    cursor.execute(add_user_query, user_data)
    conn.commit()
    print(f"Added user: {username}")

def get_user_by_username(username):
    get_user_query = "SELECT * FROM users WHERE Username = %s"
    cursor.execute(get_user_query, (username,))
    result = cursor.fetchall()
    print(result)
    if not result:
        return None
    elif len(result) == 1:
        return result[0]
    else:
        print("Warning: Multiple users found with the same username.")
        return result[0]

def delete_user(user_id):
    delete_user_query = "DELETE FROM users WHERE UserID = %s"
    cursor.execute(delete_user_query, (user_id,))
    conn.commit()
    print(f"Deleted user: {user_id}")
    return True

def update_user_byuserid(user_id, new_username=None, new_password=None, new_role_id=None):
    if not (new_username or new_password or new_role_id):
        print("No updates specified.")
        return

    update_queries = []
    values = []

    if new_username:
        check_query = "SELECT COUNT(*) FROM users WHERE Username = %s"
        cursor.execute(check_query, (new_username,))
        if cursor.fetchone()[0] > 0:
            print("New username already exists. Please choose a different one.")
            return
        else:
            update_queries.append("Username = %s")
            values.append(new_username)

    if new_password:
        new_password_hashed = hash_password(new_password)
        update_queries.append("Password = %s")
        values.append(new_password_hashed)

    if new_role_id:
        update_queries.append("RoleID = %s")
        values.append(new_role_id)

    if update_queries:
        query = "UPDATE users SET " + ", ".join(update_queries) + " WHERE UserID = %s"
        values.append(user_id)
        cursor.execute(query, tuple(values))
        conn.commit()
        print(f"Updated user with ID: {user_id}")

# 题库管理功能
def add_question(question_id, course_id, knowledge_point_id, question_type, content, options, answer, explanation, difficulty):
    add_question_query = """INSERT INTO questions (id, course_id, knowledge_point_id, question_type, content, options, answer, explanation, difficulty)
                            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"""
    question_data = (question_id, course_id, knowledge_point_id, question_type, content, options, answer, explanation, difficulty)
    cursor.execute(add_question_query, question_data)
    conn.commit()
    print(f"Added question: {content}")

def delete_question(question_id):
    delete_question_query = "DELETE FROM questions WHERE id = %s"
    cursor.execute(delete_question_query, (question_id,))
    conn.commit()
    print(f"Deleted question: {question_id}")
    return True

def update_question(question_id, course_id=None, knowledge_point_id=None, question_type=None, content=None, options=None, answer=None, explanation=None, difficulty=None):
    update_queries = []
    values = []

    if course_id:
        update_queries.append("course_id = %s")
        values.append(course_id)

    if knowledge_point_id:
        update_queries.append("knowledge_point_id = %s")
        values.append(knowledge_point_id)

    if question_type:
        update_queries.append("question_type = %s")
        values.append(question_type)

    if content:
        update_queries.append("content = %s")
        values.append(content)

    if options:
        update_queries.append("options = %s")
        values.append(options)

    if answer:
        update_queries.append("answer = %s")
        values.append(answer)

    if explanation:
        update_queries.append("explanation = %s")
        values.append(explanation)

    if difficulty:
        update_queries.append("difficulty = %s")
        values.append(difficulty)

    if update_queries:
        query = "UPDATE questions SET " + ", ".join(update_queries) + " WHERE id = %s"
        values.append(question_id)
        cursor.execute(query, tuple(values))
        conn.commit()
        print(f"Updated question with ID: {question_id}")

@app.route('/')
def home():
    return render_template('base.html')

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['Username']
        password = request.form['Password']
        hashed_password = hash_password(password)

        cursor.execute("SELECT * FROM users WHERE Username = %s AND Password = %s", (username, hashed_password))
        user = cursor.fetchone()

        if user:
            session['user_id'] = user[0]
            session['username'] = user[1]
            session['role_id'] = user[3]
            flash('登录成功！', 'success')
            if user[3] == 3:
                return redirect(url_for('admin_dashboard'))
            elif user[3] == 2:
                return redirect(url_for('teacher_dashboard'))
            elif user[3] == 1:
                return redirect(url_for('student_dashboard'))
            return redirect(url_for('home'))
        else:
            flash('无效的用户名或密码。', 'error')

    return render_template('login.html')

@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        username = request.form.get('Username')
        password = request.form.get('Password')

        # 打印表单数据
        print(f"Username: {username}, Password: {password}")

        # 检查表单数据是否完整
        if not username or not password:
            flash('所有字段都是必填项。', 'error')
            return redirect(url_for('register'))

        # 查询当前最大UserID
        cursor.execute("SELECT UserID FROM users ORDER BY UserID DESC LIMIT 1")
        max_user_id = cursor.fetchone()
        if max_user_id is None or not max_user_id[0].startswith('u'):
            new_user_id = 'u001'
        else:
            # 提取数字部分并增加1
            new_user_id = f'u{int(max_user_id[0][1:]) + 1:03d}'

        # 默认角色ID为1
        role_id = 1

        add_user(new_user_id, username, password, role_id)
        flash('注册成功！', 'success')
        return redirect(url_for('login'))
    return render_template('register.html')

@app.route('/admin')
def admin_dashboard():
    if 'role_id' in session and session['role_id'] == 3:
        cursor.execute("SELECT * FROM users")
        users = cursor.fetchall()
        return render_template('admin_dashboard.html', users=users)
    flash('未经授权的访问。', 'error')
    return redirect(url_for('login'))

@app.route('/admin/add_user', methods=['POST'])
def add_user_route():
    if 'role_id' in session and session['role_id'] == 3:
        user_id = request.form['UserID']
        username = request.form['Username']
        password = request.form['Password']
        role_id = request.form['RoleID']
        add_user(user_id, username, password, role_id)
        flash('用户添加成功！', 'success')
        return redirect(url_for('admin_dashboard'))
    flash('未经授权的访问。', 'error')
    return redirect(url_for('login'))


@app.route('/admin/edit_user/<user_id>', methods=['POST'])
def edit_user_route(user_id):
    if 'role_id' in session and session['role_id'] == 3:
        new_username = request.form['Username']
        new_role_id = request.form['RoleID']

        # 检查是否提供了新密码
        new_password = request.form.get('Password')
        if new_password:
            new_password_hashed = hash_password(new_password)
            update_user_byuserid(user_id, new_username, new_password_hashed, new_role_id)
        else:
            update_user_byuserid(user_id, new_username, None, new_role_id)  # 不更新密码

        flash('用户更新成功！', 'success')
        return redirect(url_for('admin_dashboard'))
    flash('未经授权的访问。', 'error')
    return redirect(url_for('login'))

def update_user_byuserid(user_id, new_username=None, new_password=None, new_role_id=None):
    if not (new_username or new_password or new_role_id):
        print("No updates specified.")
        return

    update_queries = []
    values = []

    if new_username:
        update_queries.append("Username = %s")
        values.append(new_username)

    if new_password:
        update_queries.append("Password = %s")
        values.append(new_password)

    if new_role_id:
        update_queries.append("RoleID = %s")
        values.append(new_role_id)

    if update_queries:
        query = "UPDATE users SET " + ", ".join(update_queries) + " WHERE UserID = %s"
        values.append(user_id)
        cursor.execute(query, tuple(values))
        conn.commit()
        print(f"Updated user with ID: {user_id}")

@app.route('/admin/delete_user/<user_id>', methods=['POST'])
def delete_user_route(user_id):
    if 'role_id' in session and session['role_id'] == 3:
        delete_user(user_id)
        flash('用户删除成功！', 'success')
        return redirect(url_for('admin_dashboard'))
    flash('未经授权的访问。', 'error')
    return redirect(url_for('login'))
# 设置Matplotlib支持中文显示
plt.rcParams['font.sans-serif'] = ['SimHei']  # 指定默认字体为黑体
plt.rcParams['axes.unicode_minus'] = False    # 解决保存图像是负号'-'显示为方块的问题

@app.route('/teacher')
def teacher_dashboard():
    if 'role_id' in session and session['role_id'] == 2:
        conn = mysql.connector.connect(**config)
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM questions")
        questions = cursor.fetchall()
        conn.close()
        return render_template('teacher_dashboard.html', questions=questions)
    flash('未经授权的访问。', 'error')
    return redirect(url_for('login'))
# 辅助函数：连接到数据库并执行查询
def query_database(query):
    conn = mysql.connector.connect(**config)
    cursor = conn.cursor()
    cursor.execute(query)
    result = cursor.fetchall()
    conn.close()
    return result

# 题目类型比例图表生成函数
def generate_question_type_pie_chart():
    query = """
    SELECT question_type, COUNT(*) AS count
    FROM questions
    GROUP BY question_type
    ORDER BY question_type;
    """
    results = query_database(query)

    labels = [row[0] for row in results]
    sizes = [row[1] for row in results]
    colors = ['#ff9999', '#66b3ff', '#99ff99', '#ffcc99', '#c2c2f0']

    plt.figure(figsize=(8, 6))
    plt.pie(sizes, labels=labels, colors=colors, autopct='%1.1f%%', startangle=140)
    plt.title(f'题库中每种题目类型的比例')
    plt.axis('equal')

    # 将图表转换为Base64编码的字符串
    buffer = BytesIO()
    plt.savefig(buffer, format='png')
    buffer.seek(0)
    chart_data = base64.b64encode(buffer.getvalue()).decode()
    plt.close()

    return chart_data

# 各知识点题目数量柱状图生成函数
def generate_knowledge_point_bar_chart():
    query = """
    SELECT kp.name AS knowledge_point, COUNT(q.id) AS question_count
    FROM questions q
    JOIN knowledge_points kp ON q.knowledge_point_id = kp.id
    GROUP BY kp.id
    ORDER BY kp.name;
    """
    results = query_database(query)

    df = pd.DataFrame(results, columns=['knowledge_point', 'question_count'])

    plt.figure(figsize=(10, 6))
    plt.bar(df['knowledge_point'], df['question_count'], color='skyblue')
    plt.xlabel('知识点')
    plt.ylabel('题目数量')
    plt.title('各知识点的题目数量')
    plt.xticks(rotation=45)

    # 将图表转换为Base64编码的字符串
    buffer = BytesIO()
    plt.savefig(buffer, format='png')
    buffer.seek(0)
    chart_data = base64.b64encode(buffer.getvalue()).decode()
    plt.close()

    return chart_data

# 不同难度问题分布柱状图生成函数
def generate_difficulty_bar_chart():
    query = '''
    SELECT CAST(difficulty AS UNSIGNED) as difficulty, COUNT(*) as count
    FROM questions
    GROUP BY difficulty
    ORDER BY difficulty;
    '''
    results = query_database(query)

    df = pd.DataFrame(results, columns=['difficulty', 'count'])
    difficulty_labels = {0: '简单', 1: '中等', 2: '困难'}
    df['difficulty_label'] = df['difficulty'].map(difficulty_labels)

    plt.figure(figsize=(10, 6))
    plt.bar(df['difficulty_label'], df['count'], color='skyblue')
    plt.xlabel('问题难度')
    plt.ylabel('题目数量')
    plt.title('不同难度的问题数量分布')
    plt.xticks(rotation=45)

    # 将图表转换为Base64编码的字符串
    buffer = BytesIO()
    plt.savefig(buffer, format='png')
    buffer.seek(0)
    chart_data = base64.b64encode(buffer.getvalue()).decode()
    plt.close()

    return chart_data
# 跳转到题库分析页面
@app.route('/teacher/analysis')
def show_analysis():
    # 生成三个图表的Base64编码字符串数据
    pie_chart = generate_question_type_pie_chart()
    knowledge_point_chart = generate_knowledge_point_bar_chart()
    difficulty_chart = generate_difficulty_bar_chart()

    # 渲染题库分析页面模板，并将图表数据传递到模板中
    return render_template('teacher_analysis.html', pie_chart=pie_chart, knowledge_point_chart=knowledge_point_chart, difficulty_chart=difficulty_chart)

# 跳转到查看试卷页面
@app.route('/teacher/view_papers')
def view_papers():
    conn = mysql.connector.connect(**config)
    cursor = conn.cursor()

    # 查询所有试卷及其试题信息
    cursor.execute("SELECT p.paper_id, p.paper_name, GROUP_CONCAT(q.id SEPARATOR ', ') AS question_ids FROM papers p LEFT JOIN paper_question pq ON p.paper_id = pq.paper_id LEFT JOIN questions q ON pq.question_id = q.id GROUP BY p.paper_id")

    papers = cursor.fetchall()

    conn.close()
    return render_template('view_papers.html', papers=papers)
# 跳转到导出试卷页面
@app.route('/teacher/export_paper')
def export_paper():
    conn = mysql.connector.connect(**config)
    cursor = conn.cursor()

    # 查询所有试卷
    cursor.execute("SELECT paper_id, paper_name FROM papers")
    papers = cursor.fetchall()

    conn.close()
    return render_template('export_paper.html', papers=papers)

from flask import send_file
from openpyxl import Workbook
import tempfile

# 下载试卷功能
@app.route('/teacher/download_paper', methods=['POST'])
def download_paper():
    paper_id = request.form['paper_id']

    # 查询试卷及其试题信息
    conn = mysql.connector.connect(**config)
    cursor = conn.cursor()

    query = """
    SELECT q.id, q.course_id, q.knowledge_point_id, q.question_type, q.content, q.options, q.answer, q.explanation, q.difficulty
    FROM questions q
    JOIN paper_question pq ON q.id = pq.question_id
    WHERE pq.paper_id = %s
    """
    cursor.execute(query, (paper_id,))
    questions = cursor.fetchall()

    # 创建一个Excel文件
    workbook = Workbook()
    sheet = workbook.active
    sheet.title = "试卷信息"

    # 添加表头
    sheet.append(["题目ID", "课程ID", "知识点ID", "题型", "内容", "选项", "答案", "解释", "难度"])

    # 添加试题信息
    for question in questions:
        sheet.append(list(question))

    # 保存文件到临时目录
    temp_file = tempfile.NamedTemporaryFile(delete=False)
    workbook.save(temp_file.name)
    temp_file.close()

    # 返回文件下载响应
    return send_file(temp_file.name, as_attachment=True, download_name="试卷导出.xlsx")

@app.route('/teacher/add_question', methods=['POST'])
def add_question_route():
    if 'role_id' in session and session['role_id'] == 2:
        question_id = request.form['QuestionID']
        course_id = request.form['CourseID']
        knowledge_point_id = request.form['KnowledgePointID']
        question_type = request.form['QuestionType']
        content = request.form['Content']
        options = request.form['Options']
        answer = request.form['Answer']
        explanation = request.form['Explanation']
        difficulty = request.form['Difficulty']
        add_question(question_id, course_id, knowledge_point_id, question_type, content, options, answer, explanation, difficulty)
        flash('题目添加成功！', 'success')
        return redirect(url_for('teacher_dashboard'))
    flash('未经授权的访问。', 'error')
    return redirect(url_for('login'))

@app.route('/teacher/edit_question/<question_id>', methods=['POST'])
def edit_question_route(question_id):
    if 'role_id' in session and session['role_id'] == 2:
        course_id = request.form['CourseID']
        knowledge_point_id = request.form['KnowledgePointID']
        question_type = request.form['QuestionType']
        content = request.form['Content']
        options = request.form['Options']
        answer = request.form['Answer']
        explanation = request.form['Explanation']
        difficulty = request.form['Difficulty']
        update_question(question_id, course_id, knowledge_point_id, question_type, content, options, answer, explanation, difficulty)
        flash('题目更新成功！', 'success')
        return redirect(url_for('teacher_dashboard'))
    flash('未经授权的访问。', 'error')
    return redirect(url_for('login'))

@app.route('/teacher/delete_question/<question_id>', methods=['POST'])
def delete_question_route(question_id):
    if 'role_id' in session and session['role_id'] == 2:
        delete_question(question_id)
        flash('题目删除成功！', 'success')
        return redirect(url_for('teacher_dashboard'))
    flash('未经授权的访问。', 'error')
    return redirect(url_for('login'))


# 批量导出试题路由
@app.route('/teacher/export_questions')
def export_questions():
    if 'role_id' in session and session['role_id'] == 2:
        query = "SELECT * FROM questions"
        cursor.execute(query)
        questions = cursor.fetchall()

        # 将试题数据转换为DataFrame
        df = pd.DataFrame(questions, columns=['id', 'course_id', 'knowledge_point_id', 'question_type', 'content', 'options', 'answer', 'explanation', 'difficulty'])

        # 将DataFrame写入Excel文件
        output = BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name='Questions')
        output.seek(0)

        return send_file(output, download_name='questions.xlsx', as_attachment=True)

    flash('未经授权的访问。', 'error')
    return redirect(url_for('login'))


@app.route('/teacher/import_questions', methods=['POST'])
def import_questions():
    if 'role_id' in session and session['role_id'] == 2:
        file = request.files['file']
        if file and file.filename.endswith('.xlsx'):
            df = pd.read_excel(file)
            for index, row in df.iterrows():
                add_question(
                    question_id=row['id'],
                    course_id=row['course_id'],
                    knowledge_point_id=row['knowledge_point_id'],
                    question_type=row['question_type'],
                    content=row['content'],
                    options=row['options'],
                    answer=row['answer'],
                    explanation=row['explanation'],
                    difficulty=row['difficulty']
                )
            flash('题目导入成功！', 'success')
        else:
            flash('无效的文件格式，请上传Excel文件。', 'error')
        return redirect(url_for('teacher_dashboard'))
    flash('未经授权的访问。', 'error')
    return redirect(url_for('login'))
@app.route('/compose_paper')
def compose_paper():
    cursor.execute("SELECT * FROM questions")
    questions = cursor.fetchall()
    return render_template('compose_paper.html', questions=questions)

# 创建试卷
@app.route('/create_paper', methods=['POST'])
def create_paper():
    if 'role_id' in session and session['role_id'] == 2:
        paper_name = request.form['paperName']
        selected_questions = request.form.getlist('selected_questions')

        # 将试卷名称存入paper表
        insert_paper_query = "INSERT INTO papers (paper_name) VALUES (%s)"
        cursor.execute(insert_paper_query, (paper_name,))
        conn.commit()

        # 获取刚刚插入的试卷ID
        paper_id = cursor.lastrowid

        # 将选中的题目插入paper_question表
        insert_paper_question_query = "INSERT INTO paper_question (paper_id, question_id) VALUES (%s, %s)"
        for question_id in selected_questions:
            cursor.execute(insert_paper_question_query, (paper_id, question_id))
        conn.commit()

        flash('试卷创建成功！', 'success')
        return redirect(url_for('teacher_dashboard'))
    flash('未经授权的访问。', 'error')
    return redirect(url_for('login'))



# 随机试卷生成路由
from random import sample


# 随机试卷生成路由
@app.route('/random_paper', methods=['POST'])
def random_paper():
    if 'role_id' in session and session['role_id'] == 2:
        try:
            # 获取试卷名称
            paper_name = request.form.get('paperName')

            # 从数据库中随机获取各类型题目
            cursor.execute("SELECT * FROM questions WHERE question_type = '选择题' ORDER BY RAND() LIMIT 3")
            multiple_choice_questions = cursor.fetchall()

            cursor.execute("SELECT * FROM questions WHERE question_type = '填空题' ORDER BY RAND() LIMIT 3")
            fill_in_the_blank_questions = cursor.fetchall()

            cursor.execute("SELECT * FROM questions WHERE question_type = '主观题' ORDER BY RAND() LIMIT 3")
            subjective_questions = cursor.fetchall()

            cursor.execute("SELECT * FROM questions WHERE question_type = '判断题' ORDER BY RAND() LIMIT 3")
            true_false_questions = cursor.fetchall()

            # 将选中的题目合并为一个列表
            random_questions = (multiple_choice_questions +
                                fill_in_the_blank_questions +
                                subjective_questions +
                                true_false_questions)[:12]  # 总共选取12道题目，可根据需要调整

            # 随机打乱题目顺序
            shuffled_questions = sample(random_questions, len(random_questions))

            # 在数据库中创建一个新试卷条目
            insert_paper_query = "INSERT INTO papers (paper_name) VALUES (%s)"
            cursor.execute(insert_paper_query, (paper_name,))
            conn.commit()

            # 获取刚插入试卷的paper_id
            paper_id = cursor.lastrowid

            # 将选定的题目插入到paper_question表中，关联到新创建的paper_id
            insert_paper_question_query = "INSERT INTO paper_question (paper_id, question_id) VALUES (%s, %s)"
            for question in shuffled_questions:
                question_id = question[0]  # 假设题目的唯一标识在元组的第一个位置
                cursor.execute(insert_paper_question_query, (paper_id, question_id))
            conn.commit()

            flash('随机试卷创建成功！', 'success')
            return redirect(url_for('teacher_dashboard'))

        except Exception as e:
            flash(f'生成随机试卷时出现错误：{str(e)}', 'error')
            return redirect(url_for('teacher_dashboard'))

    flash('未经授权的访问。', 'error')
    return redirect(url_for('login'))


@app.route('/logout')
def logout():
    session.pop('user_id', None)
    session.pop('username', None)
    session.pop('role_id', None)
    flash('成功登出！', 'success')
    return redirect(url_for('login'))
from datetime import datetime, timedelta

@app.route('/student')
def student_dashboard():
    if 'role_id' in session and session['role_id'] == 1:
        conn = mysql.connector.connect(**config)
        cursor = conn.cursor()

        cursor.execute("SELECT paper_id, paper_name FROM papers")
        papers = cursor.fetchall()

        conn.close()
        return render_template('student_dashboard.html', papers=papers)
    flash('未经授权的访问。', 'error')
    return redirect(url_for('login'))

@app.route('/student/answer_paper/<paper_id>', methods=['GET', 'POST'])
def answer_paper(paper_id):
    if 'role_id' in session and session['role_id'] == 1:
        conn = mysql.connector.connect(**config)
        cursor = conn.cursor()

        if request.method == 'POST':
            cursor.execute("SELECT COALESCE(MAX(attempt_number), 0) + 1 FROM student_answers WHERE student_id = %s AND paper_id = %s", (session['user_id'], paper_id))
            attempt_number = cursor.fetchone()[0]

            answers = request.form
            for question_id, answer in answers.items():
                insert_answer_query = "INSERT INTO student_answers (student_id, question_id, paper_id, answer, answer_time, attempt_number) VALUES (%s, %s, %s, %s, %s, %s)"
                cursor.execute(insert_answer_query, (session['user_id'], question_id.split('_')[1], paper_id, answer, datetime.now(), attempt_number))

            conn.commit()
            conn.close()
            flash('试卷提交成功！', 'success')
            return redirect(url_for('student_dashboard'))

        cursor.execute(
            "SELECT q.id, q.content, q.question_type, q.options FROM paper_question pq JOIN questions q ON pq.question_id = q.id WHERE pq.paper_id = %s",
            (paper_id,))
        questions = cursor.fetchall()

        conn.close()

        formatted_questions = []
        for question in questions:
            question_id, content, question_type, options = question
            formatted_questions.append((question_id, content, question_type, options))

        return render_template('answer_paper.html', questions=formatted_questions, paper_id=paper_id, enumerate=enumerate, chr=chr)

    flash('未经授权的访问。', 'error')
    return redirect(url_for('login'))

@app.route('/student/submit_paper/<paper_id>', methods=['POST'])
def submit_paper(paper_id):
    if 'role_id' in session and session['role_id'] == 1:
        conn = mysql.connector.connect(**config)
        cursor = conn.cursor()

        cursor.execute("SELECT COALESCE(MAX(attempt_number), 0) + 1 FROM student_answers WHERE student_id = %s AND paper_id = %s", (session['user_id'], paper_id))
        attempt_number = cursor.fetchone()[0]

        answers = request.form
        for question_id, answer in answers.items():
            insert_answer_query = "INSERT INTO student_answers (student_id, question_id, paper_id, answer, answer_time, attempt_number) VALUES (%s, %s, %s, %s, %s, %s)"
            cursor.execute(insert_answer_query, (session['user_id'], question_id.split('_')[1], paper_id, answer, datetime.now(), attempt_number))

        conn.commit()
        conn.close()
        flash('试卷提交成功！', 'success')

        return redirect(url_for('show_results', paper_id=paper_id, attempt_number=attempt_number))

    flash('未经授权的访问。', 'error')
    return redirect(url_for('login'))

@app.route('/student/results/<paper_id>/<attempt_number>')
def show_results(paper_id, attempt_number):
    if 'role_id' in session and session['role_id'] == 1:
        conn = mysql.connector.connect(**config)
        cursor = conn.cursor()

        cursor.execute("""
            SELECT sa.question_id, sa.answer, q.answer, q.knowledge_point_id, kp.name, kp.course_id, c.name 
            FROM student_answers sa 
            JOIN questions q ON sa.question_id = q.id 
            JOIN knowledge_points kp ON q.knowledge_point_id = kp.id 
            JOIN courses c ON kp.course_id = c.id
            WHERE sa.student_id = %s AND sa.paper_id = %s AND sa.attempt_number = %s
        """, (session['user_id'], paper_id, attempt_number))
        results = cursor.fetchall()

        cursor.execute("SELECT COUNT(*) FROM paper_question WHERE paper_id = %s", (paper_id,))
        total_questions = cursor.fetchone()[0]

        if total_questions == 0:
            flash('无法计算成绩，因为试卷没有题目。', 'error')
            return redirect(url_for('student_dashboard'))

        correct_count = 0
        wrong_count = 0
        wrong_knowledge_points_info = {}

        for question_id, student_answer, correct_answer, knowledge_point_id, knowledge_point_name, course_id, course_name in results:
            if student_answer == correct_answer:
                correct_count += 1
            else:
                wrong_count += 1
                if knowledge_point_id not in wrong_knowledge_points_info:
                    wrong_knowledge_points_info[knowledge_point_id] = {
                        'knowledge_point_name': knowledge_point_name,
                        'course_name': course_name,
                        'wrong_count': 0,
                        'knowledge_point_id': knowledge_point_id  # 确保加入knowledge_point_id
                    }
                wrong_knowledge_points_info[knowledge_point_id]['wrong_count'] += 1

        per_question_score = 100 / total_questions
        total_score = correct_count * per_question_score

        sorted_wrong_knowledge_points = sorted(wrong_knowledge_points_info.values(), key=lambda x: x['wrong_count'], reverse=True)
        weakest_knowledge_point = sorted_wrong_knowledge_points[0] if sorted_wrong_knowledge_points else None

        conn.close()

        return render_template('results.html',
                               total_score=total_score,
                               correct_count=correct_count,
                               wrong_count=wrong_count,
                               wrong_knowledge_points_info=sorted_wrong_knowledge_points,
                               weakest_knowledge_point=weakest_knowledge_point)

    flash('未经授权的访问。', 'error')
    return redirect(url_for('login'))

@app.route('/create_focus_study_paper', methods=['POST'])
def create_focus_study_paper():
    if 'role_id' in session and session['role_id'] == 1:  # Ensure the user is a student
        try:
            # 获取最薄弱知识点的ID
            knowledge_point_id = request.form.get('knowledge_point_id')
            if not knowledge_point_id:
                flash('未能获取到最薄弱知识点的ID', 'error')
                return redirect(url_for('student_dashboard'))

            print(f"Knowledge point ID: {knowledge_point_id}")  # 调试输出

            # 打开数据库连接
            conn = mysql.connector.connect(**config)
            cursor = conn.cursor()

            # 从数据库中获取该知识点的题目
            def fetch_question(knowledge_point_id, question_type):
                query = f"SELECT id FROM questions WHERE knowledge_point_id = %s AND question_type = '{question_type}' ORDER BY RAND() LIMIT 1"
                cursor.execute(query, (knowledge_point_id,))
                result = cursor.fetchone()
                print(f"Query: {query} with knowledge_point_id: {knowledge_point_id}, Result: {result}")  # 调试输出
                return result

            multiple_choice_question = fetch_question(knowledge_point_id, '选择题')
            true_false_question = fetch_question(knowledge_point_id, '判断题')
            fill_in_the_blank_question = fetch_question(knowledge_point_id, '填空题')
            subjective_question = fetch_question(knowledge_point_id, '主观题')

            # 将选中的题目合并为一个列表
            selected_questions = [
                multiple_choice_question,
                true_false_question,
                fill_in_the_blank_question,
                subjective_question
            ]

            print("Selected questions:", selected_questions)  # 调试输出

            # 如果没有找到任何题目，抛出异常
            if not any(selected_questions):
                raise ValueError("没有找到符合条件的题目")

            # 在数据库中创建一个新试卷条目
            insert_paper_query = "INSERT INTO papers (paper_name) VALUES (%s)"
            paper_name = f"加强学习试卷 - {knowledge_point_id}"
            cursor.execute(insert_paper_query, (paper_name,))
            conn.commit()

            # 获取刚插入试卷的paper_id
            paper_id = cursor.lastrowid  # 获取自增的paper_id

            print("Inserted paper_id:", paper_id)  # 调试输出

            # 将选定的题目插入到paper_question表中，关联到新创建的paper_id
            insert_paper_question_query = "INSERT INTO paper_question (paper_id, question_id) VALUES (%s, %s)"
            for question in selected_questions:
                if question:  # Ensure question exists
                    question_id = question[0]
                    print("Inserting question_id:", question_id)  # 调试输出
                    cursor.execute(insert_paper_question_query, (paper_id, question_id))

            conn.commit()
            cursor.close()  # 关闭游标
            conn.close()  # 关闭连接

            flash('加强学习试卷创建成功！', 'success')
            return redirect(url_for('student_dashboard'))

        except Exception as e:
            flash(f'生成加强学习试卷时出现错误：{str(e)}', 'error')
            return redirect(url_for('student_dashboard'))

    flash('未经授权的访问。', 'error')
    return redirect(url_for('login'))

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=True)
